<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class mkaryawan extends CI_Model {

    public function __construct() {
        parent::__construct();
        //$this->table = get_Class($this); 
    }
    
    function getallkaryawan(){
       
        //$sql = "SELECT * FROM main_vendor where kode_perusahaan = '$uri'";
		$sql="SELECT * from vw_datakaryawan where upper(status_kar) != 'RESIGN' order by id";
        $query = $this->db->query($sql);
        return $query->result();      
    }

    function getallkaryawan_user($user){
       
        //$sql = "SELECT * FROM main_vendor where kode_perusahaan = '$uri'";
		$sql="SELECT * from vw_datakaryawan where upper(status_kar) != 'RESIGN' and username_creator ='".$user."' order by id";
        $query = $this->db->query($sql);
        return $query->result();      
    }

    function getkaryawanresign(){
       
        //$sql = "SELECT * FROM main_vendor where kode_perusahaan = '$uri'";
		$sql="SELECT * from vw_datakaryawan where upper(status_kar) = 'RESIGN' order by id";
        $query = $this->db->query($sql);
        return $query->result();      
    }

    function getkaryawanresign_user($user){
       
        //$sql = "SELECT * FROM main_vendor where kode_perusahaan = '$uri'";
		$sql="SELECT * from vw_datakaryawan where upper(status_kar) = 'RESIGN' and username_creator ='".$user."' order by id";
        $query = $this->db->query($sql);
        return $query->result();      
    }

    function getallkaryawan_nores(){      
        //$sql = "SELECT * FROM main_vendor where kode_perusahaan = '$uri'";
		$sql="SELECT * from vw_datakaryawan where upper(status_kar) != 'RESIGN' order by id order by id";             
        $query = $this->db->query($sql);
        return $query;      
    }
    
    function getdatakaryawan($id){
       
           //$sql = "SELECT * FROM main_vendor where kode_perusahaan = '$uri'";
              $sql="SELECT * from vw_datakaryawan where id = ".$id;
              
             
              $query = $this->db->query($sql);
             
             return $query->result();      
    }

    function getdatakaryawanfilter($id_kota, $id_customer, $id_provinsi){
    	$condition = '';
    	$join_prov = '';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;
	    }
	    elseif ($id_customer != '0')
	    {
	    	$condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;	    	
	    }
	    elseif ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;
	   		
	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";
	   		
		$sql="SELECT * from vw_datakaryawan a".$join_prov."  where upper(status_kar) != 'RESIGN'".$condition;
		$query = $this->db->query($sql);
    	return $query->result();      
    }

    function getdatakaryawanfilter_user($id_kota, $id_customer, $id_provinsi,$user){
    	$condition = '';
    	$join_prov = '';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;
	    }
	    elseif ($id_customer != '0')
	    {
	    	$condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;	    	
	    }
	    elseif ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;
	   		
	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";
	   		
		$sql="SELECT * from vw_datakaryawan a".$join_prov."  where upper(status_kar) != 'RESIGN' and username_creator ='".$user."'".$condition;
		$query = $this->db->query($sql);
    	return $query->result();      
    }

    function getdatakaryawanresignfilter($id_kota, $id_customer, $id_provinsi){
    	$condition = '';
    	$join_prov = '';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;
	    }
	    elseif ($id_customer != '0')
	    {
	    	$condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;	    	
	    }
	    elseif ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;
	   		
	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";
	   		
		$sql="SELECT * from vw_datakaryawan a".$join_prov."  where upper(status_kar) = 'RESIGN'".$condition;
		$query = $this->db->query($sql);
    	return $query->result();      
    }

    function getdatakaryawanresignfilter_user($id_kota, $id_customer, $id_provinsi, $user){
    	$condition = '';
    	$join_prov = '';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;
	    }
	    elseif ($id_customer != '0')
	    {
	    	$condition .= " and id_customer=".$id_customer;
	    	if ($id_provinsi != '0')
	    		 $condition .= " and id_provinsi=".$id_provinsi;	    	
	    }
	    elseif ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;
	   		
	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";
	   		
		$sql="SELECT * from vw_datakaryawan a".$join_prov."  where upper(status_kar) = 'RESIGN' and username_creator ='".$user."'".$condition;
		$query = $this->db->query($sql);
    	return $query->result();      
    }

    function getdatakaryawanfilter_nores($id_kota, $id_customer, $id_provinsi){
    	$condition = '';
    	$join_prov ='';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    }
	    else
	    {
	    	if ($id_customer != '0')
	    		$condition .= " and id_customer=".$id_customer;
		}
	    if ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;

	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";

		$sql="SELECT * from vw_datakaryawan a".$join_prov." where upper(status_kar) != 'RESIGN'".$condition;
		$query = $this->db->query($sql);
    	return $query;      
    }

    function getdatakaryawanfilter_nores_user($id_kota, $id_customer, $id_provinsi, $user){
    	$condition = '';
    	$join_prov ='';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    }
	    else
	    {
	    	if ($id_customer != '0')
	    		$condition .= " and id_customer=".$id_customer;
		}
	    if ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;

	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";

		$sql="SELECT * from vw_datakaryawan a".$join_prov." where upper(status_kar) != 'RESIGN' and username_creator ='".$user."'".$condition;
		$query = $this->db->query($sql);
    	return $query;      
    }

    function getdatakaryawanresignfilter_nores($id_kota, $id_customer, $id_provinsi){
    	$condition = '';
    	$join_prov = '';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    }
	    else
	    {
	    	if ($id_customer != '0')
	    		$condition .= " and id_customer=".$id_customer;
		}
	    if ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;

	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";
	    	
		$sql="SELECT * from vw_datakaryawan a".$join_prov." where upper(status_kar) = 'RESIGN'".$condition;
		$query = $this->db->query($sql);
    	return $query;      
    }

    function getdatakaryawanresignfilter_nores_user($id_kota, $id_customer, $id_provinsi, $user){
    	$condition = '';
    	$join_prov = '';
    	if($id_kota != '0')
    	{
	    	$condition .= " and id_cabang=".$id_kota;
	    	if ($id_customer != '0')
	    		 $condition .= " and id_customer=".$id_customer;
	    }
	    else
	    {
	    	if ($id_customer != '0')
	    		$condition .= " and id_customer=".$id_customer;
		}
	    if ($id_provinsi != '0')
	    	$condition .= " and id_provinsi=".$id_provinsi;

	   	if ($id_provinsi != '0')
	    	$join_prov = " JOIN t_kota b on a.id_cabang = b.id_kota ";
	    	
		$sql="SELECT * from vw_datakaryawan a".$join_prov." where upper(status_kar) = 'RESIGN' and username_creator ='".$user."'".$condition;
		$query = $this->db->query($sql);
    	return $query;      
    }

    function fJsSimpanDataKaryawan($data123)
    {
    	$this->db->insert('t_datakaryawan', $data123);
    }
	function fJsUpdateDataKaryawan($data123)
	{
		//$tablename = $this->table;
		$conditions = 'id = '.$data123['id'];
		$this->db->where($conditions); 
		$this->db->update('t_datakaryawan', $data123);
	}

	function fgetallkota()
	{
		$sql="SELECT * from t_kota order by id_kota";
		$query = $this->db->query($sql);	
		return $query;
	}

	function fgetallprovinsi()
	{
		$sql="SELECT * from t_provinsi";
		$query = $this->db->query($sql);	
		return $query;
	}
	
	function fgetallcustomer()
	{
		$sql="SELECT * from t_customer";
		$query = $this->db->query($sql);	
		return $query;
	}

    function mtambahcabang($data123)
    {
    	$this->db->insert('t_kota', $data123);
    }

    function mtambahcustomer($data123)
    {
    	$this->db->insert('t_customer', $data123);
    }
    
    function mgetdatacustomer($id)
    {
		$sql="SELECT * from t_customer where id_customer=".$id;
		$query = $this->db->query($sql);	
		return $query;    	
	}
	
	function mupdatecustomer($data123)
	{
		$conditions = 'id_customer = '.$data123['id_customer'];
		$this->db->where($conditions); 
		$this->db->update('t_customer', $data123);
	}
	
	function getpassword($username)
	{
		$sql="SELECT * from t_user where username='$username'";	
		$query = $this->db->query($sql);
		return $query;
	}
	
	function gantipassword($data123)
	{
		$username = $data123['username'];
		$conditions = "username = '$username'";
		$this->db->where($conditions); 
		$this->db->update('t_user', $data123);
	}
	function getallrole()
	{
		$sql="SELECT * from t_role";	
		$query = $this->db->query($sql);
		return $query;
	}
	function mtambahuser($data123)
	{
		$this->db->insert('t_user', $data123);
	}
	function hapuskaryawan($id)
	{
		$sql="DELETE FROM t_datakaryawan where id=".$id;
		$query = $this->db->query($sql);
	}
	function hapuscustomer($id)
	{
		$sql="DELETE FROM t_customer where id_customer=".$id;
		$query = $this->db->query($sql);
	}
	function hapususer($id)
	{
		$sql="DELETE FROM t_user where username='".$id."'";
		$query = $this->db->query($sql);
	}
    public function get_kabupatenkota_data($id){
    	$sql="SELECT * FROM t_kota where id_provinsi='$id'";
        $query=$this->db->query($sql);
        if ($query->num_rows() > 0) 
        	return $query->result(); 
        
    }
	public function get_provinsi($id_kota)
	{
		$sql="SELECT id_provinsi FROM t_kota where id_kota='$id_kota'";
		$query=$this->db->query($sql)->result();
		return $query[0]->id_provinsi;
	}
	public function getalluser()
	{
		$sql="SELECT * FROM t_user";
		$query=$this->db->query($sql)->result();
		return $query;
	}
    public function mgantipenanggungjawab($id,$penanggungjawab){
         $count=0;
         
         foreach($id as $idarray){
             $did=intval($idarray);
             $this->db->where('id',$did);
             $this->db->set('username_creator',$penanggungjawab);
             $this->db->update('t_datakaryawan');
             $count=$count+1;
         }
         echo "$count data berhasil diupdate";
         $count=0;
    }
    public function insert($datarow){
        $this->db->insert("t_datakaryawan",$datarow);
    }
    public function mget_idCustomer($customername)
    {	
		$sql="select id_customer from t_customer where nama_customer = '$customername'";
		$query=$this->db->query($sql)->result();
		return $query[0]->id_customer;    	
    }
    public function mget_idCabang($namacabang)
    {	
		$sql="select id_kota from t_kota where nama_kota = '$namacabang'";
		$query=$this->db->query($sql)->result();
		return $query[0]->id_kota;    	
    }
/*
    function input_vendor($data123)
    {
        $this->db->insert('main_vendor', $data123);
        
    }

    function input_vendor_berkas($vendor,$jenisdokumen,$berlaku_sampai,$instansi,$virtual_name,$nomor_dokumen)
    {
      $sql="insert into main_vendor_dokumen (jenis_dokumen,nomor_dokumen,berlaku_sampai,instansi_pemberi,berkas,kode_perusahaan) 
      values  ('$jenisdokumen','$nomor_dokumen','$berlaku_sampai','$instansi','$virtual_name','$vendor')";
       
      $this->db->query($sql);
     
       // $this->db->insert('main_vendor_dokumen', $data);
        
    }

    function update_vendor_berkas($id_dokumen,$vendor,$jenisdokumen,$berlaku_sampai,$instansi,$virtual_name,$nomor_dokumen)
    {
       $sql="UPDATE main_vendor_dokumen 
              SET jenis_dokumen='$jenisdokumen',
                  nomor_dokumen='$nomor_dokumen',
                  berlaku_sampai='$berlaku_sampai',
                  instansi_pemberi='$instansi',
                  berkas='$virtual_name'
                  where id_dokumen='$id_dokumen'";

                 
              return $this->db->query($sql);  

    }


    function dasboardallvendor($limit=5) {


            $sql="SELECT a.kode_perusahaan, a.nama_perusahaan,a.npwp,a.alamat,
            e.nama_provinsi as provinsi,f.nama_kota as kota,a.direktur,a.fax,a.telepon,a.hp,a.email,
            a.website,a.status,a.no_ktp,a.date,
            c.deskripsi as bentukusaha,d.deskripsi as kategoriusaha
            from main_vendor a left join t_user b on a.email=b.username left join r_ven_bentuk_usaha c 
            on a.bentuk_usaha=c.kode_ref left join r_ven_kategori_usaha d on a.bentuk_usaha = d.kode_ref 
            left join r_provinsi e on a.provinsi=e.id left join r_kota f on a.kota=f.id order by kode_perusahaan desc limit $limit";
            
            
            $data = $this->db->query($sql);
      
            return $data->result();

    
    }

     function getallvendor($limit, $offset) {
          		

           $sql="SELECT a.kode_perusahaan, a.nama_perusahaan,a.npwp,a.alamat,
            e.nama_provinsi as provinsi,f.nama_kota as kota,a.direktur,a.fax,a.telepon,a.hp,a.email,
            a.website,a.status,a.no_ktp,a.date,
            c.deskripsi as bentukusaha,d.deskripsi as kategoriusaha
            from main_vendor a left join t_user b on a.email=b.username left join r_ven_bentuk_usaha c 
            on a.bentuk_usaha=c.kode_ref left join r_ven_kategori_usaha d on a.bentuk_usaha = d.kode_ref 
            left join r_provinsi e on a.provinsi=e.id left join r_kota f on a.kota=f.id order by kode_perusahaan desc limit $limit offset $offset";
            
            
            $data = $this->db->query($sql);
      
            return $data->result();

		
		
	}
   
     
        function getvendor($uri){
           
               //$sql = "SELECT * FROM main_vendor where kode_perusahaan = '$uri'";
                  $sql="SELECT a.kode_perusahaan,a.nama_perusahaan,a.npwp,a.no_ktp,a.alamat,d.nama_provinsi as provinsi,e.nama_kota as kota,a.direktur,a.telepon,
                                a.fax,a.hp,a.email,a.website,a.bentuk_usaha,a.kategori_usaha,a.status,b.deskripsi as deskripsi,c.deskripsi as kategori_usaha
                                FROM main_vendor a left join r_ven_bentuk_usaha b 
                                on a.bentuk_usaha=b.kode_ref left join r_ven_kategori_usaha c on a.bentuk_usaha=c.kode_ref 
                                left join r_provinsi d on a.provinsi=d.id join r_kota e on a.kota=e.id
                                where a.kode_perusahaan='$uri'";
                  
                 
                  $query = $this->db->query($sql);
                 
                  return $query->row();
               
                 
        }
         public function getidkota($uri)
        {
          $sql="SELECT a.id,a.nama_provinsi,b.nama_kota from r_provinsi a join r_kota b on a.id=b.fk_provinsi left join main_vendor c on c.provinsi=a.id where kode_perusahaan='$uri'";
          
           $query = $this->db->query($sql);
                       
               return $query->result();
                     

        }
        function geteditvendor($uri){
           
                $sql = "SELECT a.kode_perusahaan,a.nama_perusahaan,a.npwp,a.no_ktp,a.alamat,d.nama_provinsi as provinsi,e.nama_kota as kota,a.direktur,a.telepon,
                                a.fax,a.hp,a.email,a.website,a.bentuk_usaha,a.kategori_usaha,a.status,b.deskripsi as deskripsi,c.deskripsi as kategori_usaha
                                FROM main_vendor a left join r_ven_bentuk_usaha b 
                                on a.bentuk_usaha=b.kode_ref left join r_ven_kategori_usaha c on a.bentuk_usaha=c.kode_ref 
                                left join r_provinsi d on a.provinsi=d.id join r_kota e on a.kota=e.id
                                where a.kode_perusahaan='$uri'";
          
                $query = $this->db->query($sql);
                return $query->row();
               
                 
        }

         function updatestatusvendor($uri,$status){
           
              $sql = "update main_vendor set status='$status' where kode_perusahaan = '$uri'";
              
              return $this->db->query($sql);  
   
            
        }
         function updatevendor($kode_perusahaan,$nama_perusahaan,$npwp,$alamat,$provinsi,$kota,$direktur,$telepon,$fax,$hp,$email,$website,$bentukusaha,$kategoriusaha,$no_ktp)
         {
          
            $data=array(
            'nama_perusahaan'=>$nama_perusahaan,
            'npwp'=>$npwp,
            'alamat'=>$alamat,
            'provinsi'=>$provinsi,
            'kota'=>$kota,
            'direktur'=>$direktur,
            'telepon'=>$telepon,
            'fax'=>$fax,
            'hp'=>$hp,
            'email'=>$email,
            'website'=>$website,
            'bentuk_usaha'=>$bentukusaha,
            'kategori_usaha'=>$kategoriusaha,
            'no_ktp'=>$no_ktp);     
                        
            $this->db->where('kode_perusahaan',$kode_perusahaan);
            $this->db->update('main_vendor',$data);
             //test
             //return $this->db->query($sql);  
   
            
        }
        
    function hapusvendor($uri)
   {
    $q="DELETE FROM main_vendor WHERE kode_perusahaan='$uri'";
    $this->db->query($q);
   }

   function hapusberkas($uri)
   {
    $q="DELETE FROM main_vendor_dokumen WHERE id_dokumen='$uri'";
    $this->db->query($q);
   }

    function getbentukusaha() {
        
        $this->db->from("r_ven_bentuk_usaha");

        return $this->db->get();
    }
    function getkategoriusaha() {
        
        $this->db->from("r_ven_kategori_usaha");

        return $this->db->get();
    }
    function getprovinsi() {
        
        $this->db->from("r_provinsi");

        return $this->db->get();
    }


    function getkota() {
        
        $this->db->from("r_kota");

        return $this->db->get();
    }
     function get_state_by_country($id) {

          $sql="SELECT * FROM r_kota where fk_provinsi='$id' ";
          $query=$this->db->query($sql);
       
        if ($query->num_rows() > 0) return $query->result();              
    }    

   

     function getjenisdokumen() {
        
        $this->db->from("r_ven_jenis_dokumen");

        return $this->db->get();
    }
    
    function riwayat($uri) {
        
          $sql="SELECT a.id_pekerjaan,b.nama_perusahaan,a.nama_pekerjaan,a.status,a.pagu,a.tgl_kontrak,a.tanggal_selesai FROM main_vendor b left join main_pekerjaan a on b.kode_perusahaan=a.id_pemenang where kode_perusahaan='$uri' ";
          
          return $this->db->query($sql); 
      }
     function listdokumen($uri) {
        
          $sql="SELECT a.id_dokumen,a.jenis_dokumen,a.nomor_dokumen,a.berlaku_sampai,a.instansi_pemberi,a.berkas,a.kode_perusahaan,b.deskripsi FROM main_vendor_dokumen a left join r_ven_jenis_dokumen b on a.jenis_dokumen=b.kode_ref where kode_perusahaan='$uri' ";
          
          return $this->db->query($sql); 
      }
      
       function getdokumen($uri_dokumen) {
        
          $sql="SELECT * FROM main_vendor_dokumen where id_dokumen='$uri_dokumen' ";
          
           $query = $this->db->query($sql);
                 
                  return $query->row();

          
      }

     function simpanuser($semail,$md5_all,$sperusahaan) {
        
         $data = array(
            'username' => $semail,
            'password' => $md5_all,
            'nama'     => $sperusahaan,
            'ref_role'     => '8',
            'status'     => '0',
            );

        
         $this->db->insert('t_user', $data);
      }
      function getcodereff($nama_perusahaan,$uri)
      {
        $sql="SELECT nama,password from t_user where nama='$nama_perusahaan' and password='$uri'";
        $query = $this->db->query($sql);
                return $query->row();
      }
      function statususer($email){
           
              $sql = "update t_user set status='1' where password = '$email'";
              
              return $this->db->query($sql);  
   
            
        }

        function cek_result_ktp($post_ktp,$nama_tabel_yang_akan_dicek){
        $queryString = "SELECT * FROM $nama_tabel_yang_akan_dicek WHERE `no_ktp` = '$post_ktp'";
        $fetch = $this->db->query($queryString);     
        return $fetch->num_rows();
    }  
    */

}

?>